package Mapper.Admin;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import pojo.huiyuan;

import java.sql.*;
import java.time.LocalDateTime;

import static pojo.Mysql.*;

public class huiYuanMapper {
    public String getName(String usernameToQuery){
        String name = null;
        try (Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "SELECT name FROM huiyuan WHERE username = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setString(1, usernameToQuery);

                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()) {
                        name = resultSet.getString("name");
                        System.out.println("查询到的name为: " + name);
                    } else {
                        System.out.println("未找到匹配的用户！");
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return name;
    }

    /**
     * 获取数据
     * @param offset
     * @param limit
     * @return
     */
    public ObservableList<huiyuan> fetchDataFromDatabase(int offset, int limit) {
        ObservableList<huiyuan> data = FXCollections.observableArrayList();
        try (
                Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "SELECT * FROM huiyuan LIMIT ?, ? ";
            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, limit);

                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        String name = resultSet.getString("name");
                        String id = resultSet.getString("id");
                        String downtime = resultSet.getString("downtime");
                        String bankaqingkuang = resultSet.getString("bankaqingkuang");
                        String createtime = resultSet.getString("createtime");

                        data.add(new huiyuan(id, name, createtime, downtime,bankaqingkuang));
                    }
                }
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        }

        return data;
    }

    public void tianjia(String xingming, String huiyuanqingkuang) {
        try (Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "INSERT INTO huiyuan (name,createtime, downtime, bankaqingkuang) " +
                    "VALUES (?, ?, ?, ?)";

            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setString(1, xingming);
                preparedStatement.setObject(2, LocalDateTime.now()); // 使用当前时间
                preparedStatement.setString(3, "2044-1-12");
                preparedStatement.setString(4, huiyuanqingkuang);

                int rowsAffected = preparedStatement.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("数据添加成功！");
                } else {
                    System.out.println("数据添加失败！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public void shanchu(String id) {
        try (Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "DELETE FROM huiyuan WHERE id = ?";

            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setString(1, id);

                int rowsAffected = preparedStatement.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("数据删除成功！");
                } else {
                    System.out.println("未找到匹配的数据或删除失败！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void gengxin(String id, String xingming, String huiyuanqingkuang) {
        try (Connection connection = DriverManager.getConnection(url, user, dbPassword)) {
            String query = "UPDATE huiyuan SET name=?, bankaqingkuang=? WHERE id=?";

            try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
                preparedStatement.setString(1, xingming);
                preparedStatement.setString(2, huiyuanqingkuang);
                preparedStatement.setString(3, id);

                int rowsAffected = preparedStatement.executeUpdate();
                if (rowsAffected > 0) {
                    System.out.println("数据更新成功！");
                } else {
                    System.out.println("未找到匹配的数据或更新失败！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
